﻿'Description:装试工艺相关业务
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-07-26
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class ProcessMaintainBusiness
    ''' <summary>
    ''' 查询工艺下工序信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepDatas(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT wfs.WORKFLOWSTEPID,s.SPECNO,s.OPDESC,s.PARENTSPECNO,st.SPECTYPENAME,f.FACTORYNAME,u.ATTRIBUTEVALUE FACTORYCODE
                    ,decode(s.ISKEY,'1','是','否')ISKEY,decode(wfs.ISLASTSTEP,'1','是','否') ISLASTSTEP,T.TEAMNAME,WFS.TEAMID,PREPARECYCLE,MAKECYCLE,
                    TRANSFERCYCLE,decode(CHANGEOVERCYCLE,null,nvl(PREPARECYCLE,0)+nvl(TRANSFERCYCLE,0),CHANGEOVERCYCLE) CHANGEOVERCYCLE,
                    PARALLELGROUPNAME,wfs.WORKSTATIONIDS,'' WORKSTATIONNAME,decode(wfs.isbottleneck,'1','是','否') isbottleneck
                    FROM WORKFLOWSTEP wfs
                    LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid
                    LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid
                    LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)
                    LEFT JOIN SpecType st ON st.SPECTYPEID =s.SPECTYPEID 
                    LEFT JOIN TEAM T ON T.TEAMID = WFS.TEAMID
                    LEFT JOIN FACTORY f ON f.FACTORYID =s.FACTORYID 
                    LEFT JOIN USERATTRIBUTE u ON u.PARENTID =f.FACTORYID AND u.USERATTRIBUTENAME ='分厂代码'
                      </string>)
        sb.AppendFormat("WHERE WFS.WORKFLOWID = '{0}' AND WFS.SEQUENCE IS NOT NULL ORDER BY WFS.SEQUENCE", para("workflowId"))

        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 保存工序基础数据（班组、时间、并行工序组）
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveProcessMaintain(ByVal dt As DataTable) As Boolean
        Dim arr As New ArrayList
        For Each row As DataRow In dt.Rows
            Dim sb As New StringBuilder
            Dim isbottleneck As String = row("isbottleneck").ToString()
            If isbottleneck = "是" Then
                isbottleneck = "1"
            Else
                isbottleneck = ""
            End If
            sb.AppendFormat("UPDATE WORKFLOWSTEP SET PREPARECYCLE='{0}',MAKECYCLE='{1}',TRANSFERCYCLE='{2}',PARALLELGROUPNAME='{3}',WORKSTATIONIDS='{4}',CHANGEOVERCYCLE='{5}',isbottleneck='{6}'  WHERE WORKFLOWSTEPID='{7}'",
                            row("PREPARECYCLE").ToString, row("MAKECYCLE").ToString, row("TRANSFERCYCLE").ToString, row("PARALLELGROUPNAME").ToString, row("WORKSTATIONIDS").ToString, row("CHANGEOVERCYCLE").ToString, isbottleneck, row("WORKFLOWSTEPID").ToString)
            arr.Add(sb.ToString)
        Next
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function


    Function GetWorkStationNameByID(ByVal IDS As String) As String
        Dim name As String = String.Empty
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT wm_concat(WORKSTATIONNAME) NAME FROM WorkStation WHERE WORKSTATIONID IN('{0}')", IDS.Replace(",", "','"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            name = dt.Rows(0)("NAME").ToString
        End If
        Return name
    End Function
    ''' <summary>
    ''' 查询--工艺准备任务单（分页）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProcessTaskOrder(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                           SELECT e.EnginepartplanID,wf.description wfdesc,u.attributevalue, NVL(e.containerid,e.enginecontainerid) containerid,e.containername,mfg.mfgordername,mfg.mfgorderid,pb.productname,p.description, 
                 p.ProductRevision,wf.WorkflowRevision,wfb.workflowname,e.containerno,e.containerno togetherflag,
                 decode(e.containerid,null,'未生成','已生成') TaskBillStatus,b.bomid,e.workflowstepid,e.workflowid,
                 wfs.workflowstepname,wfs.sequence,to_char(e.plannedstartdate,'yyyy-MM-dd HH:mm') plannedstartdate, to_char(e.plannedfinishdate,'yyyy-MM-dd HH:mm') plannedfinishdate,mfg.PlannedCompletionDate,
                 pf.productfamilyname,mpb.productname mproductname,decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE,decode(e.STATUS,null,'待接收','1','准备中','已完成') STATUS
                FROM enginepartplan e 
                INNER JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
                INNER JOIN (                
                      SELECT ep.containerno,MIN(wfs.sequence) wfssequence FROM enginepartplan ep
                    LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid                    
                    WHERE ep.usecompleted='0' AND ep.containerno IS NOT NULL                    
                    GROUP BY ep.containerno                    
                    ) EP on ep.containerno=e.containerno and ep.wfssequence=wfs.sequence  
                 INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid AND mfg.productid != e.productid
                 LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
                 LEFT JOIN (select containerno,containerid,count(1) from IssueERPMaterial where ischange='0'
                 group by containerno,containerid ) im ON e.containerid=im.containerid
                 LEFT JOIN product p ON e.productid=p.productid
                 LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                 LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
                 left join workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
                 LEFT JOIN Factory f ON f.factoryid=wf.factoryid
                 LEFT JOIN (SELECT COUNT(b.Bomid) countnum,wm_concat(bt.billtypename) billtypename,
                wm_concat(DECODE(bt.billtypename,'大小件目录',b.bomid,NULL)) bomid,b.workflowid 
                from BOM b,billtype bt WHERE b.billtypeid=bt.billtypeid GROUP BY b.workflowid
                ) b ON b.workflowid=e.workflowid
                 LEFT JOIN workflow wf1 ON wf1.workflowid=wfs.workflowid
                 LEFT JOIN workflowproductfamilylist wff ON wf1.workflowid=wff.workflowid
                 LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid
                 LEFT JOIN product mp ON mfg.productid=mp.productid
                 LEFT JOIN productbase mpb ON mpb.productbaseid=NVL(mp.productbaseid,mfg.productbaseid) 
                      </string>)

        sb.AppendLine(" WHERE e.containerno is not null AND e.usecompleted='0' AND e.containerid IS NOT NULL AND NVL(e.Status,0)<>'2'")
        If para.ContainsKey("HasBom") Then
            If para("HasBom") = "1" Then
                sb.AppendLine(" AND b.bomid >'0' and INSTR(b.billtypename,'大小件目录')>0 ")
            Else
                sb.AppendLine(" and (INSTR(b.billtypename,'大小件目录')=0 OR nvl(b.billtypename ,'0')='0')")
            End If
        End If
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and e.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("PartType") Then
            If Not String.IsNullOrWhiteSpace(para("PartType")) Then
                sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("WorkFlowType") Then
            If Not String.IsNullOrWhiteSpace(para("WorkFlowType")) Then
                sb.AppendFormat(" and decode(WF.WORKFLOWTYPE,'施工工艺','0','技通工艺','0','1')='{0}' ", para("WorkFlowType"))
            End If
        End If

        sb.AppendLine("Order by e.plannedfinishdate,wfs.sequence,mfg.mfgordername,pb.productname,e.containername ASC")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 更新--工艺准备任务单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateProcessTaskOrderStatus(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim StrSql As New StringBuilder
        StrSql.AppendFormat("UPDATE ENGINEPARTPLAN SET STATUS='{0}' WHERE ENGINEPARTPLANID IN('{1}')", para("status"), para("enginepartplanID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(StrSql.ToString) > 0
    End Function
End Class
